﻿using BigMonitoringWarningApi.Model.ApiResult;
using BigMonitoringWarningApi.Tool.Extension;
using GYCTOAManagementSystemNewApi.Model;
using GYCTOAManagementSystemNewApi.Model.DTO;
using GYCTOAManagementSystemNewApi.Model.Entity;
using GYCTOAManagementSystemNewApi.Model.Enum.PayrollForm;
using GYCTOAManagementSystemNewApi.Model.Request;
using GYCTOAManagementSystemNewApi.Model.Response;
using GYCTOAManagementSystemNewApi.Model.Tool;
using GYCTOAManagementSystemNewApi.Public;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Build.ObjectModelRemoting;
using Microsoft.Extensions.Logging;
using MongoDB.Bson.IO;
using Newtonsoft.Json.Linq;
using NuGet.Packaging;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Linq;
using System.Threading.Tasks;
using WebSocketSharp.Frame;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace GYCTOAManagementSystemNewApi.Controllers
{
    /// <summary>
    /// 财务数据
    /// </summary>
    [Route("api/[controller]")]
    [ApiController]
    public class FinanceManagementController : ControllerBase
    {
        private readonly ILogger<DocumentController> _logger;
        DBContext _db;
        PublicTool _publictool;
        public FinanceManagementController(ILogger<DocumentController> logger, DBContext db)
        {
            _logger = logger;
            _db = db;
            _publictool = new PublicTool(_db);
        }

        /*  /// <summary>
          ///财务模块查看个人数据信息人员姓名、收方金额、生活费发放、报销（所有的数据）、社保
          /// </summary>
          /// <returns></returns>
          [HttpPost("SelFinanceManagement")]
          public ApiPageResult SelFinanceManagement([FromBody] SelFinanceManagementRequest Data)
          {

              try
              {
                  var UsersList = _db.Users.Where(x => !x.Delete && !x.RoleId.Contains("1"));

                  var FormUserResultList = _db.FormUser.Where(x => x.FormType == PayrollFormTypeEnum.Should
                  && x.PayrollType == OrderStateEnum.Success
                  && x.SettlementNumber == Data.SettlementNumber
                  && !x.Delete).OrderByDescending(x => x.ProjectCreateTime).ToList();


                  //生活费发放计划
                  var LivingexpensesList = _db.LivingexpensesInfo.Where(x => x.OrderStatus == RealOrderStateEnum.FinanceSuccess && !x.Delete).ToList();
                  var ReimRuleList = _db.ReimRule.Where(x => !x.Delete && x.FinanceReimbursementStart == OrderStateEnum.Success).ToList();
                  //判断是否选择了指定的人员
                  var filteredUsers = Data.UserId.HasValue ? UsersList.Where(u => u.Id == Data.UserId.Value).ToList() : UsersList.ToList();

                  var SocialinsuranceData = _db.SocialinsuranceData.Where(x => !x.Delete).ToList();

                  var data = from user in filteredUsers
                             join formuser in FormUserResultList.GroupBy(fu => fu.UserId) on user.Id equals formuser.Key into formUserGroup
                             from formUserGroupJ in formUserGroup.DefaultIfEmpty()
                             join livininfo in LivingexpensesList.GroupBy(fu => fu.UserId) on user.Id equals livininfo.Key into livingExpensesGroup
                             from livingExpensesGroupJ in livingExpensesGroup.DefaultIfEmpty()
                             join finas in ReimRuleList.GroupBy(fu => fu.UserId) on user.Id equals finas.Key into reimRuleGroup
                             from reimRuleGroupJ in reimRuleGroup.DefaultIfEmpty()
                             join socialinsurance in SocialinsuranceData.GroupBy(fu => fu.UserId) on user.Id equals socialinsurance.Key into socialinsurancegroup
                             from socialinsurancegroupJ in socialinsurancegroup.DefaultIfEmpty()
                             select new
                             {
                                 UserId = user.Id,
                                 UserName = $"{user.UserName}-{user.UserNumber}", // 人员名称
                                 FormuserTotalPrice = formUserGroup.Sum(x => x.Sum(j => j.TotalPrice)) ?? 0,
                                 LivingExpensesTotal = livingExpensesGroup.Sum(x => x.Sum(j => j?.Money ?? 0)),
                                 ReimRuleTotal = reimRuleGroup.Sum(x => x.Sum(j => j?.ReimRuleMoney ?? 0)),
                                 Balance = Convert.ToDecimal((formUserGroup.Sum(x => x.Sum(j => j.TotalPrice)) ?? 0)) - Convert.ToDecimal((livingExpensesGroup.Sum(x => x.Sum(j => j?.Money ?? 0)))),
                                 SocialInsurance = socialinsurancegroupJ.FirstOrDefault(x => x.SettlementNumber == Data.SettlementNumber)?.Socialinsurance ?? 0m
                              };
                  var query = data.ToList();
                  var total = query.Count();
                  var rows = query.Skip(Data.PageSize * (Data.PageIndex - 1)).Take(Data.PageSize).ToList();
                  return ApiPageResult.True(rows, Data.PageIndex, Data.PageSize, total);
              }
              catch (Exception ex)
              {
                  return ApiPageResult.False(new { }, Data.PageIndex, Data.PageSize);
              }

          } */
        /// <summary>
        /// 人员信息整合模块
        /// </summary>
        /// <param name="Data"></param>
        /// <returns></returns>
        [HttpPost("SelFinanceManagement")]
        public ApiPageResult SelFinanceManagement([FromBody] SelFinanceManagementRequest Data)
        {
            //获取当前登录用户信息
            var author = HttpContext.Request.Headers["Authorization"].ToString().Split(' ');
            var TokenValuesArray = TokenManage.GetTokenValues(author[1]);
            var userId = Convert.ToInt32(TokenValuesArray[0].Value);//当前账户ID
            Users userNow = _db.Users.Find(userId);
            try
            { // 优化1: 仅选择需要的字段，减少数据传输量
                IQueryable<Users> dbWhere; 
                if (Data.UserId.HasValue)
                {
                    dbWhere = _db.Users.Where(x => !x.Delete && !x.RoleId.Contains("1") && x.Id == Data.UserId);
                }
                else
                {
                    dbWhere = _db.Users.Where(x => !x.Delete && !x.RoleId.Contains("1"));
                }
                //如果登录用户为普通用户，只加载该用户的数据
                if (!userNow.RoleId.Contains("1"))
                    dbWhere = dbWhere.Where(x => x.Id == userId);
                var usersList = dbWhere.Select(u => new
                {
                    u.Id,
                    u.UserName,
                    u.UserNumber
                }).ToList();

                #region 收方工资单
                var formUserResultList = _db.FormUser.Where(x => x.SettlementNumber == Data.SettlementNumber &&x.PayrollType == OrderStateEnum.Success && x.FormType == PayrollFormTypeEnum.Should && !x.Delete).ToList();
                
                #region 筛选项目管理费，加到项目负责人的收方总金额中
                var projects = _db.Project.Where(x => !x.Delete&&x.ProjectStatus==0).ToList();
                foreach (var p in projects)
                {
                    decimal? price = 0;
                    if (!string.IsNullOrEmpty(p.ManagementfeeFT))
                    {
                        if (p.ManagementfeeFT.Contains(";"))
                        {
                            if (string.IsNullOrEmpty(Data.SettlementNumber))
                            {
                                var ft = p.ManagementfeeFT.Split(';');
                                foreach (var f in ft)
                                {
                                    price += Convert.ToInt32(f.Split('-')[1]);

                                }
                            }
                            else
                            {
                                var ft = p.ManagementfeeFT.Split(';');
                                foreach (var f in ft)
                                {
                                    if (f.Split('-')[0] == Data.SettlementNumber)
                                    {
                                        price += Convert.ToInt32(f.Split('-')[1]);
                                    }
                                }
                            }
                        }
                        else if (p.ManagementfeeFT.Contains("；"))
                        {
                            if (string.IsNullOrEmpty(Data.SettlementNumber))
                            {
                                var ft = p.ManagementfeeFT.Split('；');
                                foreach (var f in ft)
                                {
                                    price += Convert.ToInt32(f.Split('-')[1]);

                                }
                            }
                            else
                            {
                                var ft = p.ManagementfeeFT.Split('；');
                                foreach (var f in ft)
                                {
                                    if (f.Split('-')[0] == Data.SettlementNumber)
                                    {
                                        price += Convert.ToInt32(f.Split('-')[1]);
                                    }
                                }
                            }
                        }
                        else
                        {
                            if (string.IsNullOrEmpty(Data.SettlementNumber))
                            {
                                price += Convert.ToInt32(p.ManagementfeeFT.Split('-')[1]);
                            }
                            else
                            {
                                if (p.ManagementfeeFT.Split('-')[0] == Data.SettlementNumber)
                                {
                                    price += Convert.ToInt32(p.ManagementfeeFT.Split('-')[1]);
                                }
                            }
                        }
                    }
                    else
                    {
                        price += p.Managementfee;
                    }
                    var d = new FormUser
                    {
                        Id=0,
                        Delete = false,
                        FormId = 426,
                        ProjectId = p.Id,
                        UserId = Convert.ToInt32(p.HeadUserId),
                        TotalPrice = price,
                        JobContent="项目管理费",
                    };
                    if (d.TotalPrice != 0) { 
                        formUserResultList.Add(d);
                    }
                }
                #endregion
                #endregion

                #region 生活费

                var livingExpensesinfoList = _db.LivingexpensesInfo.Where(x =>x.SettlementNumber == Data.SettlementNumber   && x.OrderStatus == RealOrderStateEnum.FinanceSuccess && !x.Delete).ToList();

                #endregion

                #region 财务报销
                var reimRuleList= _db.ReimRule.Where(x =>x.SettlementNumber == Data.SettlementNumber && !x.Delete).ToList();
                #endregion
                #region 社保
                var socialInsuranceData = _db.SocialinsuranceData
                                            .Where(x => !x.Delete)
                                            .ToList();
                #endregion

                // 优化2: 使用内连接查询代替左连接，避免使用 DefaultIfEmpty() 导致的性能损失
                var data = from user in usersList
                           join formUser in formUserResultList.GroupBy(fu => fu.UserId) on user.Id equals formUser.Key into formUserGroup
                           join livingInfo in livingExpensesinfoList.GroupBy(fu => fu.UserId) on user.Id equals livingInfo.Key into livingExpensesGroup
                           join finas in reimRuleList.GroupBy(fu => fu.UserId) on user.Id equals finas.Key into reimRuleGroup
                           join socialInsurance in socialInsuranceData.GroupBy(fu => fu.UserId) on user.Id equals socialInsurance.Key into socialInsuranceGroup
                           select new
                           {
                               UserId = user.Id,
                               UserName = $"{user.UserName}-{user.UserNumber}",
                               Managementfee= (Convert.ToDecimal(formUserResultList.Where(x=>x.UserId==user.Id&&x.JobContent== "项目管理费").ToList().Sum(x => x.TotalPrice))).ToString("0.00"),
                               FormuserTotalPrice = (Convert.ToDecimal(formUserGroup.Sum(x => x.Sum(j => j.TotalPrice)) ?? 0)- Convert.ToDecimal(formUserResultList.Where(x => x.UserId == user.Id && x.JobContent == "项目管理费").ToList().Sum(x => x.TotalPrice))).ToString("0.00"),
                               FormuserInfo= formUserGroup,
                               LivingExpensesTotal = livingExpensesGroup.Sum(x => x.Sum(j => j.Money)).ToString("0.00"),
                               LivingExpensesInfo= livingExpensesGroup,
                               ReimRuleTotal = reimRuleGroup.Sum(x => x.Sum(j => j.ReimRuleMoney )).ToString("0.00"),
                               ReimRuleInfo= reimRuleGroup,
                               Balance = (Convert.ToDecimal((formUserGroup.Sum(x => x.Sum(j => j.TotalPrice)) ?? 0)) - Convert.ToDecimal((livingExpensesGroup.Sum(x => x.Sum(j => j.Money))))).ToString("0.00"),
                               SocialInsurance = (socialInsuranceData.FirstOrDefault(x => x.UserId == user.Id &&x.SettlementNumber==Data.SettlementNumber)==null?0m : socialInsuranceData.FirstOrDefault(x => x.UserId == user.Id).Socialinsurance).ToString("0.00"),
                           };

                string Empty = "0.00";
                var t3 = data.ToList();
                // 优化3: 使用 Where 过滤掉不需要的记录，避免在内存中进行计算 排除为空的记录  FormuserTotalPrice 
               var  result = t3.Where(x => x.FormuserTotalPrice != Empty || x.LivingExpensesTotal != Empty || x.ReimRuleTotal != Empty).ToList();
                var query = result;
                
                var total = query.Count();
                var rows = query.Skip(Data.PageSize * (Data.PageIndex - 1)).Take(Data.PageSize).ToList();
                return ApiPageResult.True(rows, Data.PageIndex, Data.PageSize, total);
            }
            catch (Exception ex)
            {
                return ApiPageResult.False(new { }, Data.PageIndex, Data.PageSize);
            }
        }
        /// <summary> 
        ///财务模块查看项目模块 
        /// </summary>
        /// <returns></returns>
        [HttpPost("SelFinanceManagementbyProject")]
        public async Task<ApiPageResult> SelFinanceManagementbyProject([FromBody] SelFinanceManagementbyProjectRequest Data)
        {
            IQueryable<Project> project = _db.Project.Where(x => !x.Delete);
            IQueryable<PayrollForm> payroll = _db.PayrollForm.Where(x => !x.Delete&&x.PayrollType== OrderStateEnum.Success);
            IQueryable<FinanceReimbursementReim> financeReimbursementReim = _db.FinanceReimbursementReim.Where(x => !x.Delete && x.FinanceReimbursementStart == OrderStateEnum.Success);

            if (Data.ProjectId.HasValue)
            {
                if (Data.ProjectId.Value != 0)
                {
                    project = project.Where(x => x.Id == Data.ProjectId);
                }
            }
            if (Data.FinanceReimbursementReimType.HasValue)
            {
                if (Data.FinanceReimbursementReimType.Value != 0)
                    financeReimbursementReim = financeReimbursementReim.Where(x => x.FinanceReimbursementReimType == Data.FinanceReimbursementReimType);
            }
            if (Data.ConstructionType.HasValue)
            {
                if (Data.ConstructionType.Value != 0)
                    payroll = payroll.Where(x => x.ConstructionType == Data.ConstructionType);
            }
            //查看数据年度
            if (!string.IsNullOrWhiteSpace(Data.SelectNumber))
            {
                payroll =   payroll.Where(x => x.SettlementNumber== Data.SelectNumber);
                financeReimbursementReim = financeReimbursementReim.Where(x => x.SettlementNumber == Data.SelectNumber);
            }
            var data = new object();
            var query = (from projectList in project.ToList()
                         join formuserList in payroll.ToList()
                           on projectList.Id equals formuserList.ProjectId into formUserGroup
                         join reimRuleList in financeReimbursementReim.ToList() on projectList.Id equals reimRuleList.ProjectId into reimRuleGroup
                         select new
                         {
                             ProjectName = projectList.ProjectName,
                             //收方工资单
                             FormuserMoney = formUserGroup.Sum(x => x.Totalprice),
                             one = formUserGroup.Where(x => (int)x.ConstructionType == 1).Sum(x => x.Totalprice).ToString("0.00"),
                             two = formUserGroup.Where(x => (int)x.ConstructionType == 2).Sum(x => x.Totalprice).ToString("0.00"),
                             three = formUserGroup.Where(x => (int)x.ConstructionType == 3).Sum(x => x.Totalprice).ToString("0.00"),
                             four = formUserGroup.Where(x => (int)x.ConstructionType == 4).Sum(x => x.Totalprice).ToString("0.00"),
                             five = formUserGroup.Where(x => (int)x.ConstructionType == 5).Sum(x => x.Totalprice).ToString("0.00"),
                             six = formUserGroup.Where(x => (int)x.ConstructionType == 6).Sum(x => x.Totalprice).ToString("0.00"),
                             seven = formUserGroup.Where(x => (int)x.ConstructionType == 7).Sum(x => x.Totalprice).ToString("0.00"),
                             eight = formUserGroup.Where(x => (int)x.ConstructionType == 8).Sum(x => x.Totalprice).ToString("0.00"),
                             nine = formUserGroup.Where(x => (int)x.ConstructionType == 9).Sum(x => x.Totalprice).ToString("0.00"),
                             ten = formUserGroup.Where(x => (int)x.ConstructionType == 10).Sum(x => x.Totalprice).ToString("0.00"),
                             eleven = formUserGroup.Where(x => (int)x.ConstructionType == 11).Sum(x => x.Totalprice).ToString("0.00"),
                             twelve = formUserGroup.Where(x => (int)x.ConstructionType == 12).Sum(x => x.Totalprice).ToString("0.00"),
                             thirteen = formUserGroup.Where(x => (int)x.ConstructionType == 13).Sum(x => x.Totalprice).ToString("0.00"),
                             fourteen = formUserGroup.Where(x => (int)x.ConstructionType == 14).Sum(x => x.Totalprice).ToString("0.00"),
                             fifteen = formUserGroup.Where(x => (int)x.ConstructionType == 15).Sum(x => x.Totalprice).ToString("0.00"),
                             sixteen = formUserGroup.Where(x => (int)x.ConstructionType == 16).Sum(x => x.Totalprice).ToString("0.00"),
                             seventeen = formUserGroup.Where(x => (int)x.ConstructionType == 17).Sum(x => x.Totalprice).ToString("0.00"),
                             //报销单总额
                             //ReimRule = reimRuleGroup,
                             Machinery = reimRuleGroup.Where(x => (int)x.FinanceReimbursementReimType == 1).Sum(x=>x.Money).ToString("0.00"),
                             Sporadictoolfee = reimRuleGroup.Where(x => (int)x.FinanceReimbursementReimType == 2).Sum(x => x.Money).ToString("0.00"),
                             Rent = reimRuleGroup.Where(x => (int)x.FinanceReimbursementReimType == 4).Sum(x => x.Money).ToString("0.00"), 
                             Sporadicauxiliary = reimRuleGroup.Where(x => (int)x.FinanceReimbursementReimType == 8).Sum(x => x.Money).ToString("0.00"),
                     
                             Travelexpenses = reimRuleGroup.Where(x => (int)x.FinanceReimbursementReimType == 16).Sum(x => x.Money).ToString("0.00"),
                             Other = reimRuleGroup.Where(x => (int)x.FinanceReimbursementReimType == 32).Sum(x => x.Money).ToString("0.00"),
                             ReimRuleReimRuleMoney = reimRuleGroup.Sum(x => x.Money).ToString("0.00"),
                             //基数  如果当前项目未完结则传递项目内金额：  项目完结这未 项目总金额
                             Cardinalnumber = projectList.ProjectStatus == 0 ? (projectList.EstimatedContractAmount.Value).ToString("0.00") : (projectList.SettleMoney.Value).ToString("0.00"),
                         }).ToList();
            var total = query.Count();
            var rows = query.Skip(Data.PageSize * (Data.PageIndex - 1)).Take(Data.PageSize).ToList();
            return ApiPageResult.True(rows, Data.PageIndex, Data.PageSize, total);
        } 


        /// <summary>
        ///获取收方工资单编号数据
        /// </summary>
        /// <returns></returns>
        [HttpPost("GetPayrollForm")]
        public ApiResult GetPayrollForm()
        {
            var PayrollForm = _db.PayrollForm.Where(x =>  !x.Delete).ToList();
            return ApiResult.True(PayrollForm);
        }
        /// <summary>
        ///获取报销单的单据编号信息
        /// </summary>
        /// <returns></returns>
        [HttpPost("GetReimNumber")]
        public ApiResult GetReimNumber()
        {
            var reimList = _db.FinanceReimbursementReim.Where(x => !x.Delete).ToList();
            return ApiResult.True(reimList);
        }
        /// <summary>
        ///财务模块查看个人数据信
        /// </summary>
        /// <returns></returns>
        [HttpPost("SelFinanceManagementByUserId")]
        public ApiResult SelFinanceManagementByUserId([FromBody]  SelFinanceManagementByUserIdRequest Data)
        {

            //收方工资单
            var formUserList= _db.FormUser.Where(x => x.FormType == PayrollFormTypeEnum.Should && x.PayrollType == OrderStateEnum.Success && !x.Delete && x.UserId == Data.UserId).ToList();
            //报销单
            var reimRuleList = _db.ReimRule.Where(x => x.FinanceReimbursementStart== OrderStateEnum.Success && !x.Delete && x.UserId == Data.UserId).ToList();
            var livingexpensesInfo = _db.LivingexpensesInfo.Where(x => x.OrderStatus== RealOrderStateEnum.FinanceSuccess && !x.Delete && x.UserId == Data.UserId).ToList();
            var  dataResult= formUserList.GroupBy(x=>x.ProjectId).ToList();
            var  projectlist= _db.Project.Where(x => !x.Delete).ToList();
            List<SelFinanceManagementByUserIdRespones> prop = new();
            foreach (var item in projectlist)
            {
                SelFinanceManagementByUserIdRespones finance = new();
                finance.ProjectId = item.Id;
                var userlistByProjectId = formUserList.Where(x => x.ProjectId == item.Id).ToList();
                var reimRuleListByProjectId = reimRuleList.Where(x => x.ProjectId == item.Id).ToList();
                if (reimRuleListByProjectId.Any()|| userlistByProjectId.Any())
                {
                    finance.reimRule.AddRange(reimRuleListByProjectId);
                    finance.formUsers.AddRange(userlistByProjectId);
                    prop.Add(finance);
                } 
            }
            SelFinanceManagementByUserRespones selFinance = new();
            selFinance.prop = prop;
            selFinance.LivingexpensesInfoList = livingexpensesInfo;
            return ApiResult.True(selFinance);
        }

        /// <summary>
        /// 导出年度工资单
        /// </summary>
        /// <returns></returns>
        [HttpPost("ExportYeaspayroll")]
        public async Task<ApiResult> ExportYeaspayroll([FromBody] SelFinanceManagementRequest Data)
        {
            try
            { // 优化1: 仅选择需要的字段，减少数据传输量
                IQueryable<Users> dbWhere;
                if (Data.UserId.HasValue)
                {
                    dbWhere = _db.Users.Where(x => !x.Delete && !x.RoleId.Contains("1") && x.Id == Data.UserId);
                }
                else
                {
                    dbWhere = _db.Users.Where(x => !x.Delete && !x.RoleId.Contains("1"));
                }
                var usersList = dbWhere.Select(u => new
                {
                    u.Id,
                    u.UserName,
                    u.UserNumber
                }).ToList();
                #region 收方工资单
                var formData = _db.PayrollForm
        .Where(x => !string.IsNullOrEmpty(x.SettlementNumber) && !x.Delete)
        .OrderBy(x => x.SettlementNumber)
        .ToList();
                var start = formData.FirstOrDefault(x => x.SettlementNumber == Data.SettlementNumber);
                List<PayrollForm> PayrollFormList = new();
                if (start != null)
                {
                    var endIndex = formData.IndexOf(start) + 1;
                    PayrollFormList = _db.PayrollForm
                      .Where(x => x.PayrollFormType == PayrollFormTypeEnum.Should &&
                                  x.PayrollType == OrderStateEnum.Success &&
                                  !x.Delete &&
                                x.CreateTime >= start.CreateTime)
                      .ToList();
                    if (formData.Count > endIndex)
                    {
                        PayrollFormList = PayrollFormList.Where(x => x.Id < formData[endIndex].Id).ToList();
                    }
                }
                var formUserResultList = _db.FormUser.Where(x => PayrollFormList.Select(x => x.Id).ToList().Contains(x.FormId) && !x.Delete).ToList();
                #endregion

                #region 生活费

                var livingExpenses = _db.Livingexpenses
        .Where(x => !string.IsNullOrEmpty(x.SettlementNumber) && x.OrderStatus == RealOrderStateEnum.FinanceSuccess && !x.Delete)
        .OrderBy(x => x.SettlementNumber)
        .ToList();

                List<Livingexpenses> LivingexpensesList = new();
                var livingExpensesListstart = livingExpenses.FirstOrDefault(x => x.SettlementNumber == Data.SettlementNumber);
                if (livingExpensesListstart != null)
                {
                    var livingExpensesListendIndex = livingExpenses.IndexOf(livingExpensesListstart) + 1;
                    var dt = Convert.ToDateTime(livingExpensesListstart.LivingeBatch);
                    LivingexpensesList = _db.Livingexpenses
                        .Where(x => x.OrderStatus == RealOrderStateEnum.FinanceSuccess && !x.Delete
                             )
                        .ToList();
                    LivingexpensesList = LivingexpensesList.Where(x => Convert.ToDateTime(x.LivingeBatch) >= dt).ToList();

                    if (livingExpenses.Count > livingExpensesListendIndex)
                    {
                        LivingexpensesList = LivingexpensesList.Where(x => x.Id < livingExpenses[livingExpensesListendIndex].Id).ToList();
                    }
                }
                var livingExpensesinfoList = _db.LivingexpensesInfo.Where(x => LivingexpensesList.Select(x => x.Id).ToList().Contains(x.LivingexpensesId) && !x.Delete).ToList();

                #endregion
                #region 财务报销
                var financeReimbursementReimListWhere = _db.FinanceReimbursementReim
        .Where(x => !string.IsNullOrEmpty(x.SettlementNumber) && x.FinanceReimbursementStart == OrderStateEnum.Success && !x.Delete)
        .OrderBy(x => x.SettlementNumber)
        .ToList();
                var reimRulestart = financeReimbursementReimListWhere.FirstOrDefault(x => x.SettlementNumber == Data.SettlementNumber);
                var reimRuleendIndex = financeReimbursementReimListWhere.IndexOf(reimRulestart) + 1;
                List<FinanceReimbursementReim> financeReimbursementReimList = new();
                if (reimRulestart != null)
                {
                    //报销单
                    financeReimbursementReimList = _db.FinanceReimbursementReim
                        .Where(x => x.FinanceReimbursementStart == OrderStateEnum.Success && !x.Delete &&
                         x.BorrowTime >= reimRulestart.BorrowTime)
                        .ToList();
                    if (financeReimbursementReimListWhere.Count > reimRuleendIndex)
                    {
                        financeReimbursementReimList = financeReimbursementReimList.Where(x => x.Id < financeReimbursementReimListWhere[reimRuleendIndex].Id).ToList();
                    }
                }
                var reimRuleList = _db.ReimRule.Where(x => financeReimbursementReimList.Select(x => x.Id).ToList().Contains(x.FinanceReimbursementId) && !x.Delete).ToList();
                #endregion

                var socialInsuranceData = _db.SocialinsuranceData
                                            .Where(x => !x.Delete)
                                            .ToList();
                // 优化2: 使用内连接查询代替左连接，避免使用 DefaultIfEmpty() 导致的性能损失
                var data = from user in usersList
                           join formUser in formUserResultList.GroupBy(fu => fu.UserId) on user.Id equals formUser.Key into formUserGroup
                           join livingInfo in livingExpensesinfoList.GroupBy(fu => fu.UserId) on user.Id equals livingInfo.Key into livingExpensesGroup
                           join finas in reimRuleList.GroupBy(fu => fu.UserId) on user.Id equals finas.Key into reimRuleGroup
                           join socialInsurance in socialInsuranceData.GroupBy(fu => fu.UserId) on user.Id equals socialInsurance.Key into socialInsuranceGroup
                           select new
                           {
                               UserId = user.Id,
                               UserName = $"{user.UserName}",
                               FormuserTotalPrice = (formUserGroup.Sum(x => x.Sum(j => j.TotalPrice)) ?? 0).ToString("0.00"),
                               FormuserInfo = formUserGroup,
                               LivingExpensesTotal = livingExpensesGroup.Sum(x => x.Sum(j => j.Money)).ToString("0.00"),
                               LivingExpensesInfo = livingExpensesGroup,
                               ReimRuleTotal = reimRuleGroup.Sum(x => x.Sum(j => j.ReimRuleMoney)).ToString("0.00"),
                               ReimRuleInfo = reimRuleGroup,
                               Balance = (Convert.ToDecimal((formUserGroup.Sum(x => x.Sum(j => j.TotalPrice)) ?? 0)) - Convert.ToDecimal((livingExpensesGroup.Sum(x => x.Sum(j => j.Money))))).ToString("0.00"),
                               SocialInsurance = (socialInsuranceData.FirstOrDefault(x => x.UserId == user.Id && x.SettlementNumber == Data.SettlementNumber) == null ? 0m : socialInsuranceData.FirstOrDefault(x => x.UserId == user.Id).Socialinsurance).ToString("0.00"),
                           };
                var query = data.ToList();
                List<NDGZDDto> nDGZDDtoList = new();

                foreach (var item in query)
                {
                    var nDGZDDto = new NDGZDDto();
                    nDGZDDto.UserId = item.UserId;
                    nDGZDDto.UserName = item.UserName;
                    nDGZDDto.FormuserTotalPrice = item.FormuserTotalPrice;
                    var diFormuserInfokey = item.FormuserInfo.Where(x => x.Key == item.UserId).ToList();
                    var FormUserlist = new List<FormUser>();
                    if (diFormuserInfokey.Count!=0)
                    {
                        FormUserlist.AddRange(diFormuserInfokey[0]);
                    }

                    var LivingExpensesInfoList = item.LivingExpensesInfo.Where(x => x.Key == item.UserId).ToList();
                    var LivingExpensesInfo  = new List<LivingexpensesInfo>();
                    if (LivingExpensesInfoList.Count != 0)
                    {
                        LivingExpensesInfo.AddRange(LivingExpensesInfoList[0]);
                    }

                    var ReimRuleInfo = item.ReimRuleInfo.Where(x => x.Key == item.UserId).ToList();
                    var ReimRuleInfoList = new List<ReimRule>();
                    if (LivingExpensesInfoList.Count != 0)
                    {
                        ReimRuleInfoList.AddRange(ReimRuleInfo[0]);
                    }

                    nDGZDDto.FormuserInfo = FormUserlist;
                    nDGZDDto.LivingExpensesTotal = item.LivingExpensesTotal;
                    nDGZDDto.LivingExpensesInfo = LivingExpensesInfo;
                    nDGZDDto.ReimRuleTotal = item.ReimRuleTotal;
                    nDGZDDto.ReimRuleInfo = ReimRuleInfoList;
                    nDGZDDto.Socialinsurance = Convert.ToDecimal( item.SocialInsurance);
                    nDGZDDtoList.Add(nDGZDDto);
                }
                _publictool.CreateExcelToNGZ(nDGZDDtoList, Data.SettlementNumber);
                return null;
            }
            catch (Exception ex)
            {
                return null;
            } 
        } 
  
    }
}
